﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace Freshbox2._0.UI.Controllers
{
    public class TestUrlController : BaseController<store>
    {
        //
        // GET: /TestUrl/

        StoreBll storeBll = new StoreBll();
        //
        public override ActionResult Get(int page, int rows, store model)
        {
            // int count;
            var result1 = storeBll.SearchBySQL<store>("SELECT * FROM store ");
            var count = result1.ToList();
            return Json(new { rows = result1 });
        }
        //分页查询
        public ActionResult GetOrderDatas(DateTime? starTime, DateTime? endTime)


        {
            //List<store> list = null;
            int totCount = 0;
            //
            // int index, int count,  out int totCount, 
            //
            string num = "select count(*) as num from store";//按照时间查询来显示数据量    where CreateTime >'" + starTime + "' and " + "CreateTime < '" + endTime + "'
            // string query = "SELECT TOP " + count + " * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CreateTime) AS RowNumber,* FROM VStudent where CreateTime >'" + starTime + "' and " + "CreateTime < '" + endTime + "') A WHERE RowNumber > " + count + "*( " + index + "-1" + ")";//分页查询数据，按照一页10跳数据显示出来  
            totCount = storeBll.SearchBySQL<int>(num).FirstOrDefault();   //查询总记录数
            //totCount = context.Database.SqlQuery<int>(num).FirstOrDefault();
            //list = context.Database.SqlQuery<VStudent>(query).ToList();   

            //错误的分页查询的方法   会导致内存分页
            //db.Database.SqlQuery<T>("select * from table").OrderByDescending(c => c.CreateTime).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();

            // 正确的分页查询的方法
           // row_number()从1开始，为每一条分组记录返回一个数字，
            //这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，
            //再为降序以后的没条xlh记录返回一个序号。 
            //报错
            // string sql = "select * from store";
            //string orderBy = "storeid desc";
            //int pageSize = 15;
            //int pageIndex = 1;
            //StringBuilder sb = new StringBuilder();
            //sb.Append(string.Format(@"select * from
            //(
            //select *, row_number() over (order by {0} ) as row from
            //(
            //", orderBy));
            //sb.Append(sql);
            //sb.Append(@"
            //)as t
            //)
            //            as s
            //            where s.row between " + (pageIndex * pageSize - pageSize + 1) + " and " + (pageIndex * pageSize));
           // var list2 = storeBll.SearchBySQL<store>(sb.ToString()).ToList(); 
            //where CreateTime >'" + starTime + "' and " + "CreateTime < '" + endTime + "') 
            //
            int count = 15;
            string query = "SELECT TOP " + count + " * FROM (SELECT ROW_NUMBER() OVER (ORDER BY storeid) AS RowNumber,* FROM store   WHERE RowNumber > " + count + "*( " + 1 + "-1" + ")";//分页查询数据，按照一页10跳数据显示出来  
            var  list = storeBll.SearchBySQL<store>(query).ToList();
            return Json(new { rows = list }); 
        }



    }
}
